/*
 * Copyright (c) 2013, SRI International
 * All rights reserved.
 * Licensed under the The BSD 3-Clause License;
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at:
 * 
 * http://opensource.org/licenses/BSD-3-Clause
 * 
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions
 * are met:
 * 
 * Redistributions of source code must retain the above copyright
 * notice, this list of conditions and the following disclaimer.
 * 
 * Redistributions in binary form must reproduce the above copyright
 * notice, this list of conditions and the following disclaimer in the
 * documentation and/or other materials provided with the distribution.
 * 
 * Neither the name of the aic-web-praise nor the names of its
 * contributors may be used to endorse or promote products derived from
 * this software without specific prior written permission.
 * 
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
 * FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
 * COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, 
 * INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES 
 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
 * SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) 
 * HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, 
 * STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) 
 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED
 * OF THE POSSIBILITY OF SUCH DAMAGE.
 */
package com.sri.ai.lpitools.cobra.server.data.implementations;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.xmlrpc.XmlRpcException;

import com.google.gson.Gson;
import com.sri.ai.lpitools.cobra.gwtutil.dataobjects.shared.EvidenceInfo;
import com.sri.ai.lpitools.cobra.gwtutil.dataobjects.shared.LWPException;
import com.sri.ai.lpitools.cobra.gwtutil.dataobjects.shared.ModelInfo;
import com.sri.ai.lpitools.cobra.gwtutil.dataobjects.shared.QueryInfo;
import com.sri.ai.lpitools.cobra.gwtutil.dataobjects.shared.UserInfo;
import com.sri.ai.lpitools.cobra.server.data.api.DataInterface;
import com.sri.ai.lpitools.cobra.server.data.implementations.db.SQLiteDatabase;

@SuppressWarnings("unused")
public class SQLiteDataImp implements DataInterface {	

	private static final int USER_NOT_FOUND          = -1;

	private static final int USER_TABLE_ID           = 1;
	private static final int USER_TABLE_USER         = 2;
	private static final int USER_TABLE_ADMIN        = 3;
	
	private static final int MODEL_TABLE_ID          = 1;
	private static final int MODEL_TABLE_USER        = 2;
	private static final int MODEL_TABLE_TITLE       = 3;
	private static final int MODEL_TABLE_DESCRIPTION = 4;
	private static final int MODEL_TABLE_MODEL       = 5;

	private static final int QUERY_TABLE_ID          = 1;
	private static final int QUERY_TABLE_USER        = 2;
	private static final int QUERY_TABLE_TITLE       = 3;
	private static final int QUERY_TABLE_QUERY       = 4;

	private static final int EVIDENCE_TABLE_ID       = 1;
	private static final int EVIDENCE_TABLE_USER     = 2;
	private static final int EVIDENCE_TABLE_TITLE    = 3;
	private static final int EVIDENCE_TABLE_EVIDENCE = 4;

	private SQLiteDatabase db;
	private boolean        isOpen;
	

	/*=========================================================================
	 * CONSTRUCTORS
	 *=======================================================================*/
	public SQLiteDataImp (String databaseName) {
		this(databaseName, false);
	}

	public SQLiteDataImp (String databaseName, boolean isReset) {
		try {
			db = new SQLiteDatabase(databaseName, "", "");
			db.connectToDatabase();
			initDB(isReset);
		}
		catch (ClassNotFoundException e) {
		}
		catch (SQLException e) {
		}
	}
	
	/*
	 * (non-Javadoc)
	 * @see com.sri.ai.lpitools.cobra.server.data.api.DataInterface#close()
	 */
	@Override
	public void close () throws LWPException {
		try {
			if (isOpen) {
				db.close();
				isOpen = false;
			}
		} catch (SQLException e) {
			throw new LWPException(e.getMessage());
		}
	}


	/*=========================================================================
	 * USER METHODS
	 *=======================================================================*/
	@Override
	public String getUsers() throws LWPException {
		List<UserInfo> users = new ArrayList<UserInfo>();
		if (!isOpen)
			return new Gson().toJson(users);

		try {
			PreparedStatement prep = db.prepareStatement("select * from users;");
			ResultSet set = prep.executeQuery();
			while (set.next()) {
				String name = set.getString(2);
				int admin = set.getInt(3);
				users.add(new UserInfo(name, admin > 0));
			}
		} catch (SQLException e) {
			throw new LWPException(e.getMessage());
		}
		return new Gson().toJson(users);
	}

	@Override
	public String checkUser(String user) throws LWPException {
		if (!isOpen)
			throw new LWPException("Connection not open");

		try {
			PreparedStatement prep = db.prepareStatement("select * from users where user = ?;");
			prep.setString(1, user);
			ResultSet set = prep.executeQuery();
			if (set.next()) {
				String name = set.getString(2);
				int admin = set.getInt(3);
				return new Gson().toJson(new UserInfo(name, admin > 0));
			}
		} catch (SQLException e) {
			throw new LWPException(e.getMessage());
		}
		return null;
	}

	@Override
	public boolean saveUser(String user) throws LWPException {
		if (!isOpen)
			return false;

		try {
			UserInfo userInfo = new Gson().fromJson(user, UserInfo.class);
			int result;
			
			boolean userKnown = this.checkUser(userInfo.getUser()) != null;
			
			if (!userKnown) {
				PreparedStatement prep = db.prepareStatement("replace into users (user, admin) values (?, ?);");
				prep.setString(1, userInfo.getUser());
				prep.setInt(2, userInfo.isAdmin() ? 1 : 0);
				result = prep.executeUpdate();
			}
			else {
				PreparedStatement prep = db.prepareStatement("update users set admin = ? where user = ?;");
				prep.setInt(1, userInfo.isAdmin() ? 1 : 0);
				prep.setString(2, userInfo.getUser());
				result = prep.executeUpdate();
			}
			if (result > 0)
				return true;
		}
		catch (SQLException e) {
			throw new LWPException(e.getMessage());
		}
		return false;
	}

	@Override
	public boolean deleteUser(String user) throws LWPException {
		if (!isOpen)
			return false;

		try {
			PreparedStatement prep = db.prepareStatement("delete from users where user = ?;");
			prep.setString(1, user);
			int result = prep.executeUpdate();
			if (result >= 0)
				return true;
			
		} catch (SQLException e) {
			throw new LWPException(e.getMessage());
		}
		
		return false;
	}

	
	/*=========================================================================
	 * MODEL METHODS
	 *=======================================================================*/
	@Override
	public String getModelList(String user) throws LWPException {
		List<ModelInfo> models = new ArrayList<ModelInfo>();
		if (!isOpen)
			return new Gson().toJson(models);

		try {
			int userID = getUserID(user);
			PreparedStatement prep = db.prepareStatement("select * from models where user = ?;");
			prep.setInt(1, userID);
			ResultSet set = prep.executeQuery();
			while (set.next()) {
				String title = set.getString(MODEL_TABLE_TITLE);
				String description = set.getString(MODEL_TABLE_DESCRIPTION);
				String model = set.getString(MODEL_TABLE_MODEL);
				models.add(new ModelInfo(user, title, description, model));
			}
		} catch (SQLException e) {
			throw new LWPException(e.getMessage());
		}
		return new Gson().toJson(models);
	}

	@Override
	public String openModel(String user, String modelTitle) throws LWPException {
		if (!isOpen)
			throw new LWPException("Connection not open");

		try {
			int userID = getUserID(user);
			if (userID == USER_NOT_FOUND)
				throw new LWPException("User not found");
			return openModel(user, userID, modelTitle);
		} catch (SQLException e) {
			throw new LWPException(e.getMessage());
		}
	}

	@Override
	public boolean saveModel(String user, String title, String model) throws LWPException {
		if (!isOpen)
			return false;

		try {
			int userID = getUserID(user);
			if (userID == USER_NOT_FOUND)
				return false;
			ModelInfo info = new Gson().fromJson(model, ModelInfo.class);
			//try to open the model first?
			boolean modelExists = false;
			try {
				String m = this.openModel(user, userID, title);
				if (m != null)
					modelExists = true;
			} catch (LWPException ex) {
				//indicates that the open failed, we expect this
			}
			return saveModel(userID, title, info, !modelExists);
		}
		catch (SQLException e) {
			throw new LWPException(e.getMessage());
		}
	}

	@Override
	public boolean saveModelInfo(String user, String title, String model) throws LWPException {
		if (!isOpen)
			return false;

		try {
			int userID = getUserID(user);
			if (userID == USER_NOT_FOUND)
				return false;
			ModelInfo info = new Gson().fromJson(model, ModelInfo.class);
			int result;
			
			//try to open the model first?
			boolean modelExists = false;
			try {
				String m = this.openModel(user, userID, title);
				if (m != null)
					modelExists = true;
			} catch (LWPException ex) {
				//indicates that the open failed, we expect this
			}
			
			if (!modelExists) {
				return saveModel(userID, title, info, true);
			}
			else {
				PreparedStatement prep = db.prepareStatement("update models set description = ? where user = ? and title = ?;");
				prep.setString(1, info.getShortDescription());
				prep.setInt(2, userID);
				prep.setString(3, title);
				result = prep.executeUpdate();
			}
			if (result > 0)
				return true;
		}
		catch (SQLException e) {
			throw new LWPException(e.getMessage());
		}
		return false;
	}

	@Override
	public boolean saveModelBody(String user, String title, String model) throws LWPException {
		if (!isOpen)
			return false;

		try {
			int userID = getUserID(user);
			if (userID == USER_NOT_FOUND)
				return false;
			ModelInfo info = new Gson().fromJson(model, ModelInfo.class);
			int result;
			if (this.openModel(user, userID, title) == null) {
				return saveModel(userID, title, info, true);
			}
			else {
				PreparedStatement prep = db.prepareStatement("update models set model = ? where user = ? and title = ?;");
				prep.setString(1, info.getModelText());
				prep.setInt(2, userID);
				prep.setString(3, title);
				result = prep.executeUpdate();
			}
			if (result > 0)
				return true;
		}
		catch (SQLException e) {
			throw new LWPException(e.getMessage());
		}
		return false;
	}

	@Override
	public boolean deleteModel(String user, String modelTitle) throws LWPException {
		if (!isOpen)
			return false;

		try {
			int userID = getUserID(user);
			if (userID == USER_NOT_FOUND)
				return false;

			PreparedStatement prep = db.prepareStatement("delete from models where user = ? and title = ?;");
			prep.setInt(1, userID);
			prep.setString(2, modelTitle);
			int result = prep.executeUpdate();
			if (result >= 0)
				return true;
			
		} catch (SQLException e) {
			throw new LWPException(e.getMessage());
		}
		
		return false;
	}


	/*=========================================================================
	 * QUERY METHODS
	 *=======================================================================*/
	@Override
	public String listQueries(String user) throws LWPException {
		List<QueryInfo> queries = new ArrayList<QueryInfo>();
		if (!isOpen)
			return new Gson().toJson(queries);

		try {
			int userID = getUserID(user);
			if (userID == USER_NOT_FOUND)
				return new Gson().toJson(queries);
			PreparedStatement prep = db.prepareStatement("select * from queries where user = ?;");
			prep.setInt(1, userID);
			ResultSet set = prep.executeQuery();
			while (set.next()) {
				String title = set.getString(QUERY_TABLE_TITLE);
				String query = set.getString(QUERY_TABLE_QUERY);
				queries.add(new QueryInfo(user, title, query));
			}
		} catch (SQLException e) {
			throw new LWPException(e.getMessage());
		}
		return new Gson().toJson(queries);
	}

	@Override
	public String getQuery(String user, String queryTitle) throws LWPException {
		if (!isOpen)
			throw new LWPException("Connection not open");

		try {
			int userID = getUserID(user);
			if (userID == USER_NOT_FOUND)
				throw new LWPException("User not found");
			return getQuery(user, userID, queryTitle);
		} catch (SQLException e) {
			throw new LWPException(e.getMessage());
		}
	}

	@Override
	public boolean saveQuery(String user, String title, String query) throws LWPException {
		if (!isOpen)
			return false;

		try {
			int userID = getUserID(user);
			if (userID == USER_NOT_FOUND)
				return false;
			QueryInfo info = new Gson().fromJson(query, QueryInfo.class);
			int result;
			if (this.getQuery(user, userID, title) == null) {
				PreparedStatement prep = db.prepareStatement("replace into queries (user, title, query) values (?, ?, ?);");
				prep.setInt(1, userID);
				prep.setString(2, title);
				prep.setString(3, info.getQuery());
				result = prep.executeUpdate();
			}
			else {
				PreparedStatement prep = db.prepareStatement(
						"update queries set query = ? where user = ? and title = ?;");
				prep.setString(1, info.getQuery());
				prep.setInt(2, userID);
				prep.setString(3, title);
				result = prep.executeUpdate();
			}
			if (result > 0)
				return true;
		}
		catch (SQLException e) {
			throw new LWPException(e.getMessage());
		}
		return false;
	}

	@Override
	public boolean deleteQuery(String user, String queryTitle) throws LWPException {
		if (!isOpen)
			return false;

		try {
			int userID = getUserID(user);
			if (userID == USER_NOT_FOUND)
				return false;

			PreparedStatement prep = db.prepareStatement("delete from queries where user = ? and title = ?;");
			prep.setInt(1, userID);
			prep.setString(2, queryTitle);
			int result = prep.executeUpdate();
			if (result >= 0)
				return true;
			
		} catch (SQLException e) {
			throw new LWPException(e.getMessage());
		}
		
		return false;
	}


	/*=========================================================================
	 * EVIDENCE METHODS
	 *=======================================================================*/
	@Override
	public String listEvidences(String user) throws LWPException {
		List<EvidenceInfo> evidences = new ArrayList<EvidenceInfo>();
		if (!isOpen)
			return new Gson().toJson(evidences);

		try {
			int userID = getUserID(user);
			if (userID == USER_NOT_FOUND)
				return new Gson().toJson(evidences);
			PreparedStatement prep = db.prepareStatement("select * from evidences where user = ?;");
			prep.setInt(1, userID);
			ResultSet set = prep.executeQuery();
			while (set.next()) {
				String title = set.getString(EVIDENCE_TABLE_TITLE);
				String evidence = set.getString(EVIDENCE_TABLE_EVIDENCE);
				evidences.add(new EvidenceInfo(user, title, evidence));
			}
		} catch (SQLException e) {
			throw new LWPException(e.getMessage());
		}
		return new Gson().toJson(evidences);
	}

	@Override
	public String getEvidence(String user, String evidenceTitle) throws LWPException {
		if (!isOpen)
			throw new LWPException("Connection not open");

		try {
			int userID = getUserID(user);
			if (userID == USER_NOT_FOUND)
				return null;
			return getEvidence(user, userID, evidenceTitle);
		} catch (SQLException e) {
			throw new LWPException(e.getMessage());
		}
	}

	@Override
	public boolean saveEvidence(String user, String title, String evidence) throws LWPException {
		if (!isOpen)
			return false;

		try {
			int userID = getUserID(user);
			if (userID == USER_NOT_FOUND)
				return false;
			EvidenceInfo info = new Gson().fromJson(evidence, EvidenceInfo.class);
			int result;
			if (this.getEvidence(user, userID, title) == null) {
				PreparedStatement prep = db.prepareStatement("replace into evidences (user, title, evidence) values (?, ?, ?);");
				prep.setInt(1, userID);
				prep.setString(2, title);
				prep.setString(3, info.getEvidence());
				result = prep.executeUpdate();
			}
			else {
				PreparedStatement prep = db.prepareStatement("update evidences set evidence = ? where user = ? and title = ?;");
				prep.setString(1, info.getEvidence());
				prep.setInt(2, userID);
				prep.setString(3, title);
				result = prep.executeUpdate();
			}
			if (result > 0)
				return true;
		}
		catch (SQLException e) {
			throw new LWPException(e.getMessage());
		}
		return false;
	}

	@Override
	public boolean deleteEvidence(String user, String evidenceTitle) throws LWPException {
		if (!isOpen)
			return false;

		try {
			int userID = getUserID(user);
			if (userID == USER_NOT_FOUND)
				return false;

			PreparedStatement prep = db.prepareStatement("delete from evidences where user = ? and title = ?;");
			prep.setInt(1, userID);
			prep.setString(2, evidenceTitle);
			int result = prep.executeUpdate();
			if (result >= 0)
				return true;
			
		} catch (SQLException e) {
			throw new LWPException(e.getMessage());
		}
		
		return false;
	}



	/*=========================================================================
	 * PRIVATE METHODS
	 *=======================================================================*/
	private void initDB (boolean isReset) {
		try {
			if (!isOpen) {
				// Drop the existing tables.
				if (isReset) {
					db.executeUpdate("drop table if exists users;");
					db.executeUpdate("drop table if exists models;");
					db.executeUpdate("drop table if exists queries;");
					db.executeUpdate("drop table if exists evidences;");
				}

				// Set up the new tables.
				db.executeUpdate("create table if not exists users     (userid INTEGER PRIMARY KEY, user TEXT, admin INTEGER DEFAULT 0);");
				db.executeUpdate("create table if not exists models    (modelid INTEGER PRIMARY KEY, user INTEGER, title TEXT, description TEXT, model TEXT);");
				db.executeUpdate("create table if not exists queries   (queryid INTEGER PRIMARY KEY, user INTEGER, title TEXT, query TEXT);");
				db.executeUpdate("create table if not exists evidences (evidenceid INTEGER PRIMARY KEY, user INTEGER, title TEXT, evidence TEXT);");

				isOpen = true;
			}
		}
		catch (SQLException e) {
			
		}
	}

	private int getUserID (String user) 
			throws SQLException {
		PreparedStatement prep = db.prepareStatement("select userid from users where user = ?;");
		prep.setString(1, user);
		ResultSet set = prep.executeQuery();
		if (!set.next())
			return USER_NOT_FOUND;
		return set.getInt(1);
	}
	
	private String openModel (String user, int userID, String title) throws LWPException {
		try {
			PreparedStatement prep = db.prepareStatement("select * from models where user = ? and title = ?;");
			prep.setInt(1, userID);
			prep.setString(2, title);
			ResultSet set = prep.executeQuery();
			if (set.next()) {
				String description = set.getString(MODEL_TABLE_DESCRIPTION);
				String model = set.getString(MODEL_TABLE_MODEL);
				return new Gson().toJson(new ModelInfo(user, title, description, model));
			}
		} catch (SQLException e) {
			throw new LWPException(e.getMessage());
		}
		return null;
	}

	private boolean saveModel (int userID, String title, ModelInfo info, boolean isNew) throws LWPException {
		try {
			int result;
			if (isNew) {
				PreparedStatement prep = db.prepareStatement(
						"replace into models (user, title, description, model) values (?, ?, ?, ?);");
				prep.setInt(1, userID);
				prep.setString(2, title);
				prep.setString(3, info.getShortDescription());
				prep.setString(4, info.getModelText());
				result = prep.executeUpdate();
			}
			else {
				PreparedStatement prep = db.prepareStatement(
						"update models set description = ?, model = ? where user = ? and title = ?;");
				prep.setString(1, info.getShortDescription());
				prep.setString(2, info.getModelText());
				prep.setInt(3, userID);
				prep.setString(4, title);
				result = prep.executeUpdate();
			}
			if (result > 0)
				return true;
		}
		catch (SQLException e) {
			throw new LWPException(e.getMessage());
		}
		return false;
	}

	
	private String getQuery (String user, int userID, String title) throws LWPException {
		try {
			PreparedStatement prep = db.prepareStatement("select * from queries where user = ? and title = ?;");
			prep.setInt(1, userID);
			prep.setString(2, title);
			ResultSet set = prep.executeQuery();
			if (set.next()) {
				String query = set.getString(QUERY_TABLE_QUERY);
				return new Gson().toJson(new QueryInfo(user, title, query));
			}
		} catch (SQLException e) {
			throw new LWPException(e.getMessage());
		}
		return null;
	}

	private String getEvidence (String user, int userID, String title) throws LWPException {
		try {
			PreparedStatement prep = db.prepareStatement("select * from evidences where user = ? and title = ?;");
			prep.setInt(1, userID);
			prep.setString(2, title);
			ResultSet set = prep.executeQuery();
			if (set.next()) {
				String evidence = set.getString(EVIDENCE_TABLE_EVIDENCE);
				return new Gson().toJson(new EvidenceInfo(user, title, evidence));
			}
		} catch (SQLException e) {
			throw new LWPException(e.getMessage());
		}
		return null;
	}

}
